<?php
//пароль
if (!defined('INWEB')) return2();
$a=getVar('a');
includeLang('reports');
$from=getVar('date_from','date');
$to=getVar('date_to','date');
$qfrom=date2SQLdate($from);
$qto=date2SQLdate($to);
$ins_comp_id=getVar('ins_comp', 'int',true);
$ins_type_id=getVar('ins_type', 'int',true);
$report=getVar('report_type');
$parse=$Lang;
$parse['page']=$page;
 
$parse['ins_comp_opt']='';
$sql->query("SELECT id, abbr FROM insurance_companies");
while($ic=$sql->fetch_array())
{
    $selected=($ins_comp_id==$ic['id'])?' selected="selected"':'';
    $parse['ins_comp_opt'].='<option value="'.$ic['id'].'"'.$selected.'>'.$ic['abbr'].'</option>';
}

$parse['ins_type_opt']='';
$sql->query("SELECT id, name FROM ins_type");
while($it=$sql->fetch_array())
{
    $selected=($ins_type_id==$it['id'])?' selected="selected"':'';
    $parse['ins_type_opt'].='<option value="'.$it['id'].'"'.$selected.'>'.$it['name'].'</option>';
}
if(getVar('ins_comp_all')=='on')
    $parse['ins_comp_all']='checked="checked"';
if(getVar('ins_type_all'))
    $parse['ins_type_all']='checked="checked"';
if(getVar('report_type')=='contracts')
    $parse['contracts_s']='checked="checked"';
if(getVar('report_type')=='income')
    $parse['income_s']='checked="checked"';
$parse['date_from_v']=$from;
$parse['date_to_v']=$to;
$tpl->parsetemplate('finance',$parse);

if($_POST || isset($_GET['save']) || isset($_GET['pg']))
{
    try
    {
        if(!isset($_GET['save']))
        {
            if($report=='contracts')
            {
                if(getVar('ins_comp_all')=='on')
                {
                    $q_ins_comp_add='';
                }
                else
                {
                    $q_ins_comp_add=" AND policies.ins_comp_id='$ins_comp_id'";
                }
                if(getVar('ins_type_all')=='on')
                {
                    $q_ins_type_add='';
                }
                else
                {
                    $q_ins_type_add=" AND policies.ins_type_id='$ins_type_id'";
                }
                echo '<table border="1"><tr><td>'.$Lang['concluded_contracts'].'</td></tr>';
                echo '<tr><td>'.sprintf($Lang['time_period_from_to'],$from,$to).'</td></tr>';
                if($q_ins_comp_add!='')
                {
                    $ic=$sql->query("SELECT `name` FROM insurance_companies WHERE id='$ins_comp_id';");
                    $icname=$sql->result($ic);
                    echo '<tr><td>'.$Lang['insurance_company'].' '.$icname.'</td></tr>';
                }
                if($q_ins_type_add!='')
                {
                    $it=$sql->query("SELECT `name` FROM ins_type WHERE id='$ins_type_id';");
                    $itname=$sql->result($it);
                    echo '<tr><td>'.$Lang['insurance_type'].' '.$itname.'</td></tr>';
                }
                echo '</table><table border="1">';
                if(isset($_GET['pg']))
	               $start = getVar('pg');
                else 
        	       $start = 1;
                if(!is_numeric($start) || $start == 0)
	               $start = 1;
                $start = abs($start) - 1;
                $startlimit = $start * getConfig('settings','items_per_page','100');
                $rows=getConfig('settings','items_per_page','100');
                $npage=$page;
                $npage.='&report_type='.$report;
                $npage.='&date_from='.$from;
                $npage.='&date_to='.$to;
                $npage.='&ins_comp='.$ins_comp_id;
                $npage.='&ins_type='.$ins_type_id;
                $npage.='&ins_comp_all='.getVar('ins_comp_all');
                $npage.='&ins_type_all='.getVar('ins_type_all');
                $page_foot=$sql->query("SELECT count(*) FROM policies CROSS JOIN objects RIGHT JOIN pol_obj ON(policies.id=pol_obj.pId AND objects.id=pol_obj.oId) LEFT JOIN canceled_policies ON policies.id=canceled_policies.id INNER JOIN insurance_companies ON policies.ins_comp_id=insurance_companies.id INNER JOIN ins_type ON policies.ins_type_id=ins_type.id INNER JOIN currency ON policies.currency_id=currency.id LEFT JOIN clients as c1 ON policies.taker_id=c1.id LEFT JOIN clients as c2 ON policies.owner_id=c2.id LEFT JOIN employees ON policies.employee_id=employees.id WHERE canceled_policies.id IS NULL AND policies.issue_date>='$qfrom' AND policies.issue_date<='$qto' $q_ins_comp_add $q_ins_type_add;");
                $page_foot = $sql->result($page_foot);
                $query="SELECT policies.nr, policies.add_nr, insurance_companies.name as IC_NAME, ins_type.`name` as IT_NAME, c1.name as taker, c2.name as owner, IF(objects.tId=1,concat(objects.manufacturer,', ', objects.model,', ', objects.reg_nr), objects.loc) as objekts, policies.issue_date, policies.from_date, policies.to_date, currency.abbr as crc_name, policies.ins_prem, policies.add_disc_rate, policies.comm_rate, policies.broker_rate, employees.name as employee FROM policies CROSS JOIN objects RIGHT JOIN pol_obj ON(policies.id=pol_obj.pId AND objects.id=pol_obj.oId) LEFT JOIN canceled_policies ON policies.id=canceled_policies.id INNER JOIN insurance_companies ON policies.ins_comp_id=insurance_companies.id INNER JOIN ins_type ON policies.ins_type_id=ins_type.id INNER JOIN currency ON policies.currency_id=currency.id LEFT JOIN clients as c1 ON policies.taker_id=c1.id LEFT JOIN clients as c2 ON policies.owner_id=c2.id LEFT JOIN employees ON policies.employee_id=employees.id WHERE canceled_policies.id IS NULL AND policies.issue_date>='$qfrom' AND policies.issue_date<='$qto' $q_ins_comp_add $q_ins_type_add LIMIT $startlimit, $rows";
                echo '<tr><th>'.$Lang['nr'].'</th><th>'.$Lang['add_nr'].'</th><th>'.$Lang['ic'].'</th><th>'.$Lang['type'].'</th><th>'.$Lang['taker_owner'].'</th><th>'.$Lang['ins_object'].'</th><th>'.$Lang['issue_date'].'</th><th>'.$Lang['from_date'].'</th><th>'.$Lang['to_date'].'</th><th>'.$Lang['currency'].'</th><th>'.$Lang['signed_premium'].'</th><th>'.$Lang['disc_perc'].'</th><th>'.$Lang['comm_perc'].'</th><th>'.$Lang['broker_perc'].'</th><th>'.$Lang['employee'].'</th></tr>';
    
                $sql->query($query);
                while($row=$sql->fetch_array())
                {
                    $issdate=explode(' ',$row['issue_date']);
                    $frdate=explode(' ',$row['from_date']);
                    $todate=explode(' ',$row['to_date']);
                    echo '<tr><td>'.$row['nr'].'</td><td>'.$row['add_nr'].'</td><td>'.$row['IC_NAME'].'</td><td>'.$row['IT_NAME'].'</td><td>'.$row['taker'].' / '.$row['owner'].'</td><td>'.$row['objekts'].'</td><td>'.$issdate[0].'</td><td>'.$frdate[0].'</td><td>'.$todate[0].'</td><td>'.$row['crc_name'].'</td><td>'.$row['ins_prem'].'</td><td>'.$row['add_disc_rate'].'</td><td>'.$row['comm_rate'].'</td><td>'.$row['broker_rate'].'</td><td>'.$row['employee'].'</td></tr>';
                    if($row['crc_name']=='')
                        $row['crc_name']='Other';
                    if(!isset($curr[$row['crc_name']]))
                    {
                        $curr[$row['crc_name']]=array();
                        $curr[$row['crc_name']]['cnt']=0;
                        $curr[$row['crc_name']]['prem']=0;
                        $curr[$row['crc_name']]['comm']=0;
                        $curr[$row['crc_name']]['dsc']=0;
                        $curr[$row['crc_name']]['brok']=0;
                    }
                    $curr[$row['crc_name']]['cnt']++;
                    $curr[$row['crc_name']]['prem']+=$row['ins_prem'];
                    if($row['comm_rate']>0)
                    {
                        $curr[$row['crc_name']]['comm']+=$row['comm_rate']/100*$row['ins_prem'];
                    }
                    if($row['add_disc_rate']>0)
                    {
                        $curr[$row['crc_name']]['dsc']+=$row['add_disc_rate']/100*$row['ins_prem'];
                    }
                    if($row['broker_rate']>0)
                    {
                        $curr[$row['crc_name']]['brok']+=$row['broker_rate']/100*$row['ins_prem'];
                    }
                }
                echo '</table>';
                echo pagechoose($start + 1, $page_foot, $npage);
            }
            else
            {
                echo '<table border="1"><tr><td><b>'.$Lang['planned_income'].'</b></td></tr>'."\n";
                echo '<tr><td><b>'.sprintf($Lang['time_period_from_to'],$from,$to).'</b></td></tr></table>'."\n";
                $sql->query("SELECT currency.abbr, SUM(payments.sum) as SUMMA, SUM(IF(ISNULL(policies.comm_rate),0,payments.sum*policies.comm_rate/100)) AS comm, SUM(IF(ISNULL(policies.add_disc_rate),0,payments.sum*policies.add_disc_rate/100)) as add_disc, SUM(IF(ISNULL(policies.broker_rate),0,payments.sum*policies.broker_rate/100)) AS broker FROM policies LEFT JOIN canceled_policies ON policies.id=canceled_policies.id LEFT JOIN payments ON policies.id=payments.id LEFT JOIN currency ON policies.currency_id=currency.id WHERE canceled_policies.id IS NULL AND currency_id IS NOT NULL AND payments.pay_date>='$qfrom' AND payments.pay_date<='$qto' GROUP BY policies.currency_id");
                while($ccur=$sql->fetch_array())
                {
                    if(isset($curr[$ccur['abbr']]))
                    {
                        err($Lang['error'], $Lang['currency_already_set']);
                        exit;
                    }
                    else
                    {
                        $curr[$ccur['abbr']]=array();
                        $curr[$ccur['abbr']]['prem']=$ccur['SUMMA'];
                        $curr[$ccur['abbr']]['comm']=$ccur['comm'];
                        $curr[$ccur['abbr']]['dsc']=$ccur['add_disc'];
                        $curr[$ccur['abbr']]['brok']=$ccur['broker'];
                    }
                }
            }
            echo '<br />';
            if(count($curr)>0)
            {
                echo '<table border="1"><tr><td>'.$Lang['total'].'</td>';
                foreach($curr as $key=>$values)
                {
                    echo '<td>'.$key.'</td>';
                }
                echo '</tr>';
                if($report=='contracts')
                {
                    echo '<tr><td>'.$Lang['policy_count'].'</td>';
                    foreach($curr as $key=>$values)
                    {
                        echo '<td>'.$curr[$key]['cnt'].'</td>';
                    }
                    echo '</tr>';
                }
                echo '<tr><td>'.$Lang['signed_premium'].'</td>';
                foreach($curr as $key=>$values)
                {
                    echo '<td>'.round($curr[$key]['prem'],2).'</td>';
                }
                echo '</tr>';
                echo '<tr><td>'.$Lang['signed_commision'].'</td>';
                foreach($curr as $key=>$values)
                {
                    echo '<td>'.round($curr[$key]['comm'],2).'</td>';
                }
                echo '</tr>';
                echo '<tr><td>'.$Lang['signed_discount'].'</td>';
                foreach($curr as $key=>$values)
                {
                    echo '<td>'.round($curr[$key]['dsc'],2).'</td>';
                }
                echo '</tr>';
                echo '<tr><td>'.$Lang['signed_broker_perc'].'</td>';
                foreach($curr as $key=>$values)
                {
                    echo '<td>'.round($curr[$key]['brok'],2).'</td>';
                }
                echo '</tr></table>';
            }
            echo '<a href="?p='.$page.'&date_from='.$from.'&date_to='.$to.'&ins_comp='.$ins_comp_id.'&ins_type='.$ins_type_id.'&report_type='.$report.'&ins_comp_all='.getVar('ins_comp_all').'&ins_type_all='.getVar('ins_type_all').'&save"><img src="img/save.png" alt="'.$Lang['save'].'" title="'.$Lang['save'].'" /></a>';
        }
        else
        {
        $content=writeXLSHead()."\n";
        echo $Lang['generating_xml_content'].$report.'<br />';
        if($report=='contracts')
        {
            if(getVar('ins_comp_all')=='on')
            {
                $q_ins_comp_add='';
            }
            else
            {
                $q_ins_comp_add=" AND policies.ins_comp_id='$ins_comp_id'";
            }
            if(getVar('ins_type_all')=='on')
            {
                $q_ins_type_add='';
            }
            else
            {
                $q_ins_type_add=" AND policies.ins_type_id='$ins_type_id'";
            }
            $content.='<Row><Cell ss:StyleID="s23"><Data ss:Type="String">'.$Lang['concluded_contracts'].'</Data></Cell></Row>'."\n";
            $content.='<Row><Cell><Data ss:Type="String">'.sprintf($Lang['time_period_from_to'],$from,$to).'</Data></Cell></Row>'."\n";
            if($q_ins_comp_add!='')
            {
                $ic=$sql->query("SELECT `name` FROM insurance_companies WHERE id='$ins_comp_id';");
                $icname=$sql->result($ic);
                $content.='<Row><Cell><Data ss:Type="String">'.$Lang['insurance_company'].' '.$icname.'</Data></Cell></Row>'."\n";
            }
            if($q_ins_type_add!='')
            {
                $it=$sql->query("SELECT `name` FROM ins_type WHERE id='$ins_type_id';");
                $itname=$sql->result($it);
                $content.='<Row><Cell><Data ss:Type="String">'.$Lang['insurance_type'].' '.$itname.'</Data></Cell></Row>'."\n";
            }
            $query="SELECT policies.nr, policies.add_nr, insurance_companies.name as IC_NAME, ins_type.`name` as IT_NAME, c1.name as taker, c2.name as owner, IF(objects.tId=1,concat(objects.manufacturer,', ', objects.model,', ', objects.reg_nr), objects.loc) as objekts, policies.issue_date, policies.from_date, policies.to_date, currency.abbr as crc_name, policies.ins_prem, policies.add_disc_rate, policies.comm_rate, policies.broker_rate, employees.name as employee FROM policies CROSS JOIN objects RIGHT JOIN pol_obj ON(policies.id=pol_obj.pId AND objects.id=pol_obj.oId) LEFT JOIN canceled_policies ON policies.id=canceled_policies.id INNER JOIN insurance_companies ON policies.ins_comp_id=insurance_companies.id INNER JOIN ins_type ON policies.ins_type_id=ins_type.id INNER JOIN currency ON policies.currency_id=currency.id LEFT JOIN clients as c1 ON policies.taker_id=c1.id LEFT JOIN clients as c2 ON policies.owner_id=c2.id LEFT JOIN employees ON policies.employee_id=employees.id WHERE canceled_policies.id IS NULL AND policies.issue_date>='$qfrom' AND policies.issue_date<='$qto' $q_ins_comp_add $q_ins_type_add";
            $content.='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['add_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ic'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['type'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['taker_owner'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ins_object'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['issue_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['from_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['to_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['currency'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_premium'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['disc_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['comm_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['employee'].'</Data></Cell></Row>';

            $sql->query($query);
            while($row=$sql->fetch_array())
            {
                $issdate=explode(' ',$row['issue_date']);
                $frdate=explode(' ',$row['from_date']);
                $todate=explode(' ',$row['to_date']);
                $content .='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['add_nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IC_NAME'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IT_NAME'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['taker'].' / '.$row['owner'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['objekts'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$issdate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$frdate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$todate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['crc_name'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['ins_prem'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['add_disc_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['comm_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['broker_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['employee'].'</Data></Cell></Row>'."\n";
                if($row['crc_name']=='')
                    $row['crc_name']='Other';
                if(!isset($curr[$row['crc_name']]))
                {
                    $curr[$row['crc_name']]=array();
                    $curr[$row['crc_name']]['cnt']=0;
                    $curr[$row['crc_name']]['prem']=0;
                    $curr[$row['crc_name']]['comm']=0;
                    $curr[$row['crc_name']]['dsc']=0;
                    $curr[$row['crc_name']]['brok']=0;
                }
                $curr[$row['crc_name']]['cnt']++;
                $curr[$row['crc_name']]['prem']+=$row['ins_prem'];
                if($row['comm_rate']>0)
                {
                    $curr[$row['crc_name']]['comm']+=$row['comm_rate']/100*$row['ins_prem'];
                }
                if($row['add_disc_rate']>0)
                {
                    $curr[$row['crc_name']]['dsc']+=$row['add_disc_rate']/100*$row['ins_prem'];
                }
                if($row['broker_rate']>0)
                {
                    $curr[$row['crc_name']]['brok']+=$row['broker_rate']/100*$row['ins_prem'];
                }
            }
        }
        else
        {
            $content.='<Row><Cell ss:StyleID="s23"><Data ss:Type="String">'.$Lang['planned_income'].'</Data></Cell></Row>'."\n";
            $content.='<Row><Cell><Data ss:Type="String">'.sprintf($Lang['time_period_from_to'],$from,$to).'</Data></Cell></Row>'."\n";
            $sql->query("SELECT currency.abbr, SUM(payments.sum) as SUMMA, SUM(IF(ISNULL(policies.comm_rate),0,payments.sum*policies.comm_rate/100)) AS comm, SUM(IF(ISNULL(policies.add_disc_rate),0,payments.sum*policies.add_disc_rate/100)) as add_disc, SUM(IF(ISNULL(policies.broker_rate),0,payments.sum*policies.broker_rate/100)) AS broker FROM policies LEFT JOIN canceled_policies ON policies.id=canceled_policies.id LEFT JOIN payments ON policies.id=payments.id LEFT JOIN currency ON policies.currency_id=currency.id WHERE canceled_policies.id IS NULL AND currency_id IS NOT NULL AND payments.pay_date>='$qfrom' AND payments.pay_date<='$qto' GROUP BY policies.currency_id");
            while($ccur=$sql->fetch_array())
            {
                if(isset($curr[$ccur['abbr']]))
                {
                    err($Lang['error'], $Lang['currency_already_set']);
                    exit;
                }
                else
                {
                    $curr[$ccur['abbr']]=array();
                    $curr[$ccur['abbr']]['prem']=$ccur['SUMMA'];
                    $curr[$ccur['abbr']]['comm']=$ccur['comm'];
                    $curr[$ccur['abbr']]['dsc']=$ccur['add_disc'];
                    $curr[$ccur['abbr']]['brok']=$ccur['broker'];
                }
            }
        }
    $content .='<Row></Row>'."\n";
    if(count($curr)>0)
    {
    $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['total'].'</Data></Cell>';
    foreach($curr as $key=>$values)
    {
        $content .= '<Cell ss:StyleID="s22"><Data ss:Type="String">'.$key.'</Data></Cell>';
    }
    $content .='</Row>'."\n";
    if($_POST['report_type']=='contracts' || $_GET['report_type']=='contracts')
    {
        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['policy_count'].'</Data></Cell>';
        foreach($curr as $key=>$values)
        {
            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.$curr[$key]['cnt'].'</Data></Cell>';
        }
        $content .='</Row>'."\n";
    }
    $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_premium'].'</Data></Cell>';
    foreach($curr as $key=>$values)
    {
        $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['prem'],2).'</Data></Cell>';
    }
    $content .='</Row>'."\n";
    $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_commision'].'</Data></Cell>';
    foreach($curr as $key=>$values)
    {
        $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['comm'],2).'</Data></Cell>';
    }
    $content .='</Row>'."\n";
    $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_discount'].'</Data></Cell>';
    foreach($curr as $key=>$values)
    {
        $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['dsc'],2).'</Data></Cell>';
    }
    $content .='</Row>'."\n";
    $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_broker_perc'].'</Data></Cell>';
    foreach($curr as $key=>$values)
    {
        $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['brok'],2).'</Data></Cell>';
    }

    $content .='</Row>'."\n";
    }
    $content .=writeXLSFoot();
    $fn=md5($qfrom.$qto.getVar('report_type').getVar('ins_comp','int',true).getVar('ins_type','int',true).time());
    writeFile('files/'.$fn.'.xml',$content);
    echo '<a href="download.php?id='.$fn.'.xml&del=1">'.$Lang['download_file'].'</a>';
    }
    }
    catch(Exception $e)
    {
        msg($Lang['error'],$e->getMessage(),'error');
    }
}
?>